package com.estar.integaration.Util;

import cn.hutool.core.date.DatePattern;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.estar.integaration.Service.IMaterialService;
import com.estar.integaration.Service.IProductDbService;
import com.estar.integaration.entity.Material;
import com.estar.integaration.entity.MaterialVo;
import com.estar.integaration.entity.ProductDb;
import com.estar.integaration.entity.ProductMachineInfo;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Author: YJL
 * Date: 2021/11/18 13:31
 **/
public class MExcelUtil {
  public static Logger logger = LoggerFactory.getLogger(MExcelUtil.class);

  /**
   *
   * @param excelName  表名
   * @param entityClass 实体类
   * @param fieldMap 实体类字段和中文的对应关系
   * @param start 开始
   * @param sheetIndex sheet的index
   * @param <T>
   * @param
   * @return
   * @throws IOException
   */
  public static <T>ProductMachineInfo parseExcel(String excelName, Class<T> entityClass, HashMap<String,String> fieldMap, int start, int sheetIndex, IProductDbService productDbService, IMaterialService materialService,String info) throws IOException, IllegalAccessException, InstantiationException {
    //读入文件
    XSSFWorkbook wb;
    //校验
    try(InputStream inputStream = new FileInputStream(new File(excelName))){
      wb = new XSSFWorkbook(inputStream);
    }
    if(entityClass == null){
      throw new IllegalArgumentException("实体类不可为空");
    }
    if(fieldMap == null || fieldMap.isEmpty()){
      throw new IllegalArgumentException("字段map不可为空");
    }
    //
    HashMap<String,Integer> letter2ColMap = new HashMap<>(); //ABC和123..的关系
    for(Map.Entry<String,String> entry : fieldMap.entrySet()){
      String cnNormalName = entry.getKey();
      int index = getExcelColumIndex(cnNormalName);
      letter2ColMap.put(cnNormalName,index); //得到从中文名到index的对应
    }
    //返回的list
    List<T> resultList = new ArrayList<>();
    Sheet sheet = wb.getSheetAt(sheetIndex);
    ProductMachineInfo productMachineInfo = initMachineInfo(sheet);
    try{
      //将sheet转为list
      logger.info(String.valueOf(sheet.getLastRowNum()));
      // sheet.getRow(1).getCell(1); 获取某一个
      //外层循环行，获取index表的最大行数
      String productDbId = productMachineInfo.getProductDb().getId();
      for(int i = start ; i <= sheet.getLastRowNum() - 2 ; i++){  //少读两行0
        T entity = entityClass.newInstance();
        Row row =  sheet.getRow(i);
        for(Map.Entry<String,String> entry : fieldMap.entrySet()){
          //获取bean的属性名称
          String enNormalName = entry.getValue();
          //根据ABC获取列号
          int col = letter2ColMap.get(entry.getKey());//ABCD->对象名 ABCD->12345
          //获得当前单元格中间的内容
          Cell cell = null;
          try {
            cell = row.getCell(col);
          }catch (Exception e){
            logger.info("多行异常");
            throw new NumberFormatException("第" + String.valueOf(i+1) + "行超出解析范围");
          }
          if(col == 7){//现在由第六个变成第七个 planquantity->quantity
            Integer content = null;
            String cellV = (String)getCellValue(cell);

            if(cellV == null)
              continue;
            if(!StringUtil.isNumeric(cellV)){
              info = "第" + String.valueOf(i+1) + "行" + "第"  + String.valueOf(col) + "列出现错误,请检查单元格内容与格式。";
              throw new NumberFormatException(info);
            }
            content = Integer.parseInt(cellV);
            setFieldValueByName(enNormalName, content,entity);
            logger.info(String.valueOf(content));
          } else {
            String content = null;
            content = (String) getCellValue(cell);
            setFieldValueByName(enNormalName, content,entity);
            logger.info(String.valueOf(content));
          }
          //TODO 这里加上审批编号
          setFieldValueByName("productDbId", productDbId ,entity);
          //给对象赋值
        }
        //多A码 多机台号 直接取0-9给采购
        setFieldValueByName("approvalCode", productMachineInfo.getProductDb().getProductApprovalCode() ,entity);
        resultList.add(entity);
      }
      productMachineInfo.setMaterialVOList(resultList);
    } catch (ParseException e) {
      e.printStackTrace();
    }finally {

      if(wb != null) wb.close();

    }

    return productMachineInfo;

  }

  /**
   * 重构解析Excel
   * @param excelName
   * @param <T>
   * @return
   */
  public static <T>ProductMachineInfo HparseExcel(String excelName) throws IOException {

    ProductMachineInfo productMachineInfo = new ProductMachineInfo();

    ExcelReader excelReader = null;

    List<List<Object>> content = null;

    try(InputStream inputStream = new FileInputStream(new File(excelName))){

      excelReader = ExcelUtil.getReader(inputStream,0);

      content = excelReader.read(0,excelReader.getRowCount());

      try {

        productMachineInfo = parseContent(content);

      }catch (NumberFormatException e){

        throw e;

      }

      return productMachineInfo;

    } catch (FileNotFoundException e) {

      e.printStackTrace();

      throw e;

    } catch (IOException e) {

      e.printStackTrace();
      throw e;

    }

  }

  /**
   * 解析从Excel中解析出来的内容
   * @param content
   * @param <T>
   * @return
   */
  public static <T>ProductMachineInfo parseContent(List<List<Object>> content){

    ProductMachineInfo productMachineInfo = new ProductMachineInfo();

    ProductDb productDb;
    try {
      productDb = initProductDb(content);
    }catch (NumberFormatException e){
      throw e;
    }


    productMachineInfo.setProductDb(productDb);

    List<MaterialVo> materialVoList = new ArrayList<>();

    int maxRow = 0;

    for(List<Object> list : content){

      if(list.get(0).equals("日期：")){

        maxRow = content.indexOf(list);

      }

    }

    for(int i = 4 ; i < maxRow - 1 ; i++ ){

      MaterialVo materialVo = initMaterialVo(content.get(i));

      materialVo.setProductId(productDb.getId());

      materialVo.setACode(productDb.getACode());

      materialVoList.add(materialVo);

    }

    productMachineInfo.setMaterialVOList(materialVoList);

    return productMachineInfo;


  }


  private static MaterialVo initMaterialVo(List<Object> row){

    MaterialVo materialVo = new MaterialVo();

    materialVo.setId(Util.getUUID());

    materialVo.setMaterialNo(String.valueOf(row.get(1)));

    materialVo.setMaterialName(String.valueOf(row.get(2)));

    materialVo.setFigureNo( String.valueOf(row.get(3)));

    materialVo.setSpecs(String.valueOf(row.get(4)));

    materialVo.setBrand(String.valueOf(row.get(5)));

    materialVo.setQuantity(Double.parseDouble(String.valueOf(row.get(6))));

    materialVo.setPlanQuantity(Double.parseDouble(String.valueOf(row.get(7))));

    materialVo.setPrice(String.valueOf(row.get(8)));

//    materialVo.setTotalPrice((Double)row.get(9));

    materialVo.setTotalPrice(null);

    materialVo.setBomRemark(String.valueOf(row.get(10)));

    return materialVo;

  }


  /**
   * 从Excel中弄出ProductDb
   * @param content
   * @return
   */
  private static ProductDb initProductDb(List<List<Object>> content){

    List<Object> content1 = content.get(0);List<Object> content2 = content.get(1);List<Object> content3 = content.get(2);

    ProductDb productDb = new ProductDb();

    productDb.setId(Util.getUUID());

    productDb.setACode(String.valueOf(content1.get(1)).trim());

    productDb.setProductName(String.valueOf(content1.get(4)));

    productDb.setProductType(String.valueOf(content1.get(5)));

    productDb.setVersionCode(String.valueOf((content1.get(7))));

    productDb.setProductFigureCode(String.valueOf((content2.get(1))));

    productDb.setProduceCode(String.valueOf(content2.get(4)));

    productDb.setProductApprovalCode(String.valueOf(content2.get(6)));

    try {
      productDb.setProductQuantity(Integer.parseInt(String.valueOf(content3.get(1))));
    }catch (NumberFormatException e){
      throw new NumberFormatException("产品数量未填,请填写后上传");
    }


    productDb.setProductSpec(String.valueOf(content3.get(4)));

    productDb.setProductDeliveryTime(null);

    return productDb;

  }





  public static int getExcelColumIndex(String headerChars){
    if(headerChars == null || !headerChars.matches("[a-zA-Z]+")){
      throw new IllegalArgumentException("无效的key" + headerChars + ",key要是26个字母");
    }
    headerChars = headerChars.toUpperCase();
    char char0 = headerChars.charAt(0);

    if(headerChars.length() == 1){
      return (int)char0 - 65;
    }
    char char1 = headerChars.charAt(1);
    return ((int)char0-64)*26 + (int)char1 - 65;
  }

  /**
   *  根据字段名设置字段内容
   * @param fieldName
   * @param fieldValue
   */
  public static void setFieldValueByName(String fieldName,Object fieldValue,Object o ) throws IllegalAccessException, ParseException {
    Field field = getFieldByName(fieldName,o.getClass());
    if(field != null){
      field.setAccessible(true);
      //获取字段类型
      Class<?> fieldType = field.getType();
      String str = ObjectUtils.toString(fieldValue,"");
      if(StringUtils.isBlank(str)){
        field.set(o,null);
      }else {
        //所有Double类型转为Integer类型处理
        if (String.class == fieldType) {
          field.set(o, String.valueOf(fieldValue));
        } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
          field.set(o, Integer.parseInt(fieldValue.toString()));
        } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
          field.set(o, Long.valueOf(fieldValue.toString()));
        } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
          field.set(o, Float.valueOf(fieldValue.toString()));
        } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
          field.set(o, Short.valueOf(fieldValue.toString()));
        } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
          field.set(o, Double.valueOf(fieldValue.toString()));
        } else if (Character.TYPE == fieldType) {
          if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
            field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));
          }
        } else if (Date.class == fieldType) {
          if(!String.valueOf(str).startsWith("2")){
            field.set(o, DateUtils.parseDate("", DatePattern.NORM_DATE_PATTERN));
          }else {
            field.set(o, DateUtils.parseDate(fieldValue.toString(), DatePattern.NORM_DATE_PATTERN));
          }
        } else {
          field.set(o, fieldValue);
        }
      }
    }
  }

  /**
   * 根据字段名获取字段
   * @param fieldName  字段名
   * @param clazz  包含该字段的类
   * @return
   */
  public static Field getFieldByName(String fieldName,Class<?> clazz){
    Field[] selfFields = clazz.getDeclaredFields();
    //本类中存在字段则返回
    for(Field field : selfFields){
      if(field.getName().equals(fieldName)){
        return field;
      }
    }
    //若没有则返回空
    return null;
  }

  /**
   * 初始化MachineInfo的内容
   * @param sheet
   * @return
   */
  private static ProductMachineInfo initMachineInfo(Sheet sheet){
    ProductDb productDb = new ProductDb();
    productDb.setId(Util.getUUID());
    ProductMachineInfo productMachineInfo = new ProductMachineInfo();
    productDb.setACode((String) getCellValue(sheet.getRow(0).getCell(1)));
    productDb.setProductName((String) getCellValue(sheet.getRow(0).getCell(4)));
    String type = (String) getCellValue(sheet.getRow(0).getCell(5));
    type = type.replaceAll(" ","");
    productDb.setProductType(type);
    productDb.setVersionCode((String) getCellValue(sheet.getRow(0).getCell(7)));
    productDb.setProductFigureCode((String) getCellValue(sheet.getRow(1).getCell(1)));
    productDb.setProduceCode((String) getCellValue(sheet.getRow(1).getCell(4)));
    productDb.setProductApprovalCode((String) getCellValue(sheet.getRow(1).getCell(6)));
    productDb.setProductQuantity(Integer.parseInt((String) getCellValue(sheet.getRow(2).getCell(1))));
    productDb.setProductSpec((String) getCellValue(sheet.getRow(2).getCell(6)));
    productDb.setProductDeliveryTime(null);//日期暂定
    productMachineInfo.setProductDb(productDb);
    return productMachineInfo;

  }

  /**
   * 来的type是 0 就是处理成int
   * 1处理成 String
   * @param cell
   * @param
   * @return
   */
  public static Object getCellValue(Cell cell){
    Object content = null;
    if(cell == null){
      return content;
    }
    switch (cell.getCellType()){
      case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
      case Cell.CELL_TYPE_NUMERIC:
        if(DateUtil.isCellDateFormatted(cell)){
          Date date = cell.getDateCellValue();
          DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
          content = df.format(date);
          String dateContent = String.valueOf(content).replace("/","-");
          return dateContent;
        }else {
          String str = String.valueOf(cell.getNumericCellValue());
          String[] sepa = str.split("\\.");
          return sepa[0];
        }
      case Cell.CELL_TYPE_FORMULA:
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getStringCellValue();
      default:
        return content;
    }
  }

  /**
   * 取到真实的A_code
   * @param productId
   * @return
   */
  public static String getRealProductId(String productId){
    if(productId.contains(",")){
      String productIdWith_ = productId.split(",")[0];
      if(productId.contains("-")){
        productIdWith_ = productIdWith_.split("-")[0];
      }
      return productIdWith_;
    }else if(productId.contains("-")){
      productId = productId.split("-")[0];
      return productId;
    }else {
      return productId;
    }
  }


  /**
   * 初始化技术部技术部的对应hash
   * @return
   */
  public static HashMap<String,String> initMaterialVOHash(){
    HashMap<String,String> techHashFieldMap = new HashMap<>();
    techHashFieldMap.put("B","materialNo");
    techHashFieldMap.put("C","materialName");
    techHashFieldMap.put("D","figureNo");
    techHashFieldMap.put("E","specs");
    techHashFieldMap.put("F","brand");
    techHashFieldMap.put("G","quantity");
    techHashFieldMap.put("H","planQuantity");
    techHashFieldMap.put("I","price");
    techHashFieldMap.put("J","totalPrice");
    techHashFieldMap.put("K","bomRemark");
    return techHashFieldMap;
  }

  /**
   *
   * @return
   */
  public static HashMap<String, String> initProductHash(){
    HashMap<String,String> productHashFieldMap = new HashMap<>();
    productHashFieldMap.put("B","custom");
    productHashFieldMap.put("C","productName");
    productHashFieldMap.put("D","leaveFacCode");
    productHashFieldMap.put("E","productionOrderNo");
    productHashFieldMap.put("F","quantity");
    productHashFieldMap.put("G","electricalNo");
    productHashFieldMap.put("H","color");
    productHashFieldMap.put("I","orderDate");
    productHashFieldMap.put("J","erpDate");
    productHashFieldMap.put("K","designer");
    productHashFieldMap.put("L","issueDate");
    productHashFieldMap.put("M","plotDate");
    productHashFieldMap.put("N","plmCreateTime");
    productHashFieldMap.put("O","bomListCreate");
    productHashFieldMap.put("P","bomProductionSupplementDate");
    productHashFieldMap.put("Q","bomAftermarketSupplementDate");
    productHashFieldMap.put("R","machinedDate");
    productHashFieldMap.put("S","standardDate");
    productHashFieldMap.put("T","assemblyMachineryDate");
    productHashFieldMap.put("U","assemblyElectricalDate");
    productHashFieldMap.put("V","shipDate");
    productHashFieldMap.put("W","assemblyCrew");
    productHashFieldMap.put("X","isBegin");
    productHashFieldMap.put("Y","beginDate");
    productHashFieldMap.put("Z","materialLastComeDate");
    productHashFieldMap.put("AA","storageDate");
    productHashFieldMap.put("AB","deliveryDate");
    productHashFieldMap.put("AC","remarks");
    productHashFieldMap.put("AD","progress");
    return productHashFieldMap;
  }

  public static HashMap<String,String> initPurchaseHash(){
    HashMap<String,String> MaterialFieldMap = new HashMap<>();
    MaterialFieldMap.put("A","materialNo");
    MaterialFieldMap.put("B","materialName");
    MaterialFieldMap.put("C","figureNo");
    MaterialFieldMap.put("D","specs");
    MaterialFieldMap.put("E","brand");
    MaterialFieldMap.put("F","quantity");
    MaterialFieldMap.put("G","price");  //改动3 以防万一
    MaterialFieldMap.put("H","planQuantity");
    MaterialFieldMap.put("I","totalPrice");
    MaterialFieldMap.put("J","bomRemark");
    MaterialFieldMap.put("K","equipmentNo");
    MaterialFieldMap.put("L","delivery");
    MaterialFieldMap.put("M","purchased");
    MaterialFieldMap.put("N","unpurchased");
    MaterialFieldMap.put("O","thisPurchase");
    return MaterialFieldMap;
  }







}
